package cn.harvest.storage.bo.manager;

import java.util.List;

import cn.harvest.storage.dao.DAO;
import cn.harvest.storage.po.Invoice;
import cn.harvest.storage.po.Merchandise;

public class InvoiceService {

	private DAO dao = DAO.getDAO();

	private InvoiceService() {

	}

	public static InvoiceService getService() {

		InvoiceService service = new InvoiceService();
		return service;
	}

	public boolean create(Invoice invoice) {

		return dao.create(invoice);
	}
	
	public boolean update(Invoice invoice) {
		
		return dao.update(invoice);
	}

	public List selectInvoiceItems(String invoiceId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT m.merchandise_name, m.merchandise_norm, it.merchandise_price, it.merchandise_amount,");
		sql.append(" 	    (SELECT mu.merchandise_unit_name FROM merchandise_unit mu WHERE mu.merchandise_unit_id = m.merchandise_unit_id),");
		sql.append(" 	    it.merchandise_total");
		sql.append(" FROM invoice_item it,merchandise m");
		sql.append(" WHERE it.merchandise_id = m.merchandise_id ");
		sql.append(" AND it.invoice_id = " + invoiceId);
		
		System.out.println("### - InvoiceService -> selectInvoiceItems -> #SQL \r\n	 " + sql);
		
		return dao.selectBySQL(sql.toString());
	}
	
	public List selectInvoice(String[] Condition){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT inv.invoice_id, inv.invoice_cid,");
		sql.append("        (SELECT s.supplier_name FROM supplier s WHERE s.supplier_id = inv.supplier_id),");
		sql.append("        inv.invoice_type_id,");
		sql.append("        (SELECT emp.employee_name FROM employee emp WHERE emp.employee_id = inv.employee_id),");
		sql.append("        inv.invoice_date,");
		sql.append("        inv.invoice_total,");
		sql.append("        (SELECT sh.storehouse_name FROM storehouse sh WHERE sh.storehouse_id = inv.storehouse_id),");
		sql.append("        (SELECT emp.employee_name FROM employee emp WHERE emp.employee_id = inv.operator_id),");
		sql.append("        inv.invoice_memo");
		sql.append(" FROM invoice inv");
		sql.append(" WHERE 1 = 1");
		if(!(null==Condition[0]||"".equals(Condition[0]))){
			sql.append(" AND inv.invoice_type_id = '"+Condition[0]+"'");
		}
		if (!(null == Condition[1] || "".equals(Condition[1]))) {
			sql.append(" AND inv.invoice_cid LIKE '%" + Condition[1] + "%'");
		}
		if (!(null == Condition[2] || "".equals(Condition[2]))) {
			sql.append(" AND STR_TO_DATE(inv.invoice_date, '%Y-%m-%d') >= STR_TO_DATE(" + Condition[3] + ", '%Y-%m-%d')");
		}
		if (!(null == Condition[3] || "".equals(Condition[3]))) {
			sql.append(" AND STR_TO_DATE(inv.invoice_date, '%Y-%m-%d') <= STR_TO_DATE(" + Condition[4] + ", '%Y-%m-%d')");
		}
		sql.append(" ORDER BY inv.invoice_id");
		
		System.out.println("### - InvoiceService -> selectInvoice -> #SQL \r\n	 " + sql);
		
		return dao.selectBySQL(sql.toString());
	}
	
	public List selectSupplier(String type){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT s.supplier_id, s.supplier_name");
		sql.append(" FROM supplier s");
		sql.append(" WHERE s.supplier_type = " + type);
		sql.append(" ORDER BY s.supplier_id");

		System.out.println("### - InvoiceService -> selectSupplier -> #SQL \r\n	 " + sql);
		
		return dao.selectBySQL(sql.toString());
	}

	public List selectWarehouse(){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sh.storehouse_id, sh.storehouse_name");
		sql.append(" FROM storehouse sh");
		sql.append(" ORDER BY sh.storehouse_id");

		System.out.println("### - InvoiceService -> selectWarehouse -> #SQL \r\n	 " + sql);
		
		return dao.selectBySQL(sql.toString());
	}

	public List selectAttn(){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT em.employee_id, em.employee_name FROM employee em ");

		System.out.println("### - InvoiceService -> selectAttn -> #SQL \r\n	 " + sql);
		
		return dao.selectBySQL(sql.toString());
	}
	
	public List selectBySQL(Merchandise merchandise,String parentType) {

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT m.merchandise_id, m.merchandise_cid, m.merchandise_name, mt.merchandise_type_name, m.merchandise_norm,");
		sql.append("		m.merchandise_amount, mu.merchandise_unit_name, m.merchandise_alert, m.merchandise_memo, mt.merchandise_type_parent,");
		sql.append("		m.merchandise_type_id, m.merchandise_unit_id");
		sql.append(" FROM merchandise m, merchandise_type mt, merchandise_unit mu");
		sql.append(" WHERE 1=1");
		sql.append(" AND m.merchandise_unit_id = mu.merchandise_unit_id");
		sql.append(" AND m.merchandise_type_id = mt.merchandise_type_id");
		if (null != merchandise.getMerchandiseId()) {
			sql.append(" AND m.merchandise_id = " + merchandise.getMerchandiseId());
		}
		if (null != merchandise.getMerchandiseName()) {
			sql.append(" AND m.merchandise_name LIKE '%" + merchandise.getMerchandiseName() + "%'");
		}
		if (null != merchandise.getMerchandiseType()) {
			sql.append(" AND m.merchandise_type_id = " + merchandise.getMerchandiseType().getMerchandiseTypeId());
		}
		if (null != parentType && !"".equals(parentType)) {
			sql.append(" AND mt.merchandise_type_parent = " + parentType);
		}
		sql.append(" ORDER BY m.merchandise_id;");

		System.out.println("### - InvoiceService -> selectBySQL -> #SQL \r\n	 " + sql);
		
		return dao.selectBySQL(sql.toString());
	}
	
	public Merchandise selectByHQL(String merchandiseId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" FROM Merchandise m WHERE m.merchandiseId = " + merchandiseId);
		
		System.out.println("### - InvoiceService -> selectByHQL -> #HQL \r\n	 " + hql);
		
		Merchandise merchandise = (Merchandise) dao.selectByHQL(hql.toString()).get(0);
		return merchandise;
	}
	
	public static void main(String[] args) {

	}
}
